package com.pachong;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

/**
 * CREATE TABLE `service_values_20180622` (
 *   `ID_DEVICE` varchar(50) NOT NULL,
 *   `ID_SERVICE` varchar(50) NOT NULL,
 *   `TAG_NAME` varchar(100) NOT NULL,
 *   `DATE_TIME` datetime NOT NULL,
 *   `PV` double DEFAULT NULL,
 *   `EU` varchar(20) DEFAULT NULL
 * ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='2018-06-22历史数据表';
 */
public class InsertBigData {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        batchInsert();
    }


    public static void batchInsert() throws ClassNotFoundException, SQLException{
        long start = System.currentTimeMillis();
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://192.168.129.10:3306/pachong?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&characterEncoding=UTF-8",
                "root", "root");

        connection.setAutoCommit(false);
        String prefix = "INSERT INTO service_values_20180622 (ID_DEVICE," +
                "ID_SERVICE," +
                "TAG_NAME," +
                "DATE_TIME," +
                "PV) VALUES (?,?,?,?,?)";

        PreparedStatement cmd = connection
                .prepareStatement(prefix);

        for (int i = 0; i < 500000;i++){//100万条数据
            cmd.setString(
                    1, "aaaa");
            System.err.println("插入的ID是:aaaa");
            cmd.setString(2, "bbbb");
            cmd.setString(3, "增压泵故障");
            long time = System.currentTimeMillis();
            cmd.setDate(4,new java.sql.Date(time));
            cmd.setDouble(5,22.2);
            cmd.addBatch();
            if(i%100000==0){//10
                System.err.println("一万条提交一次:");
                cmd.executeBatch();
                connection.commit();
            }
        }
        cmd.executeBatch();
        connection.commit();

        cmd.close();
        connection.close();

        long end = System.currentTimeMillis();
        System.out.println("批量插入需要时间:"+(end - start)); //批量插入需要时间:24675
    }
}
